-- The purpose of this demo is to show how different languages sort differently.
-- © Erland Sommarskog 2024.

-- This function returns all non-deprecated collations in SQL Server.
SELECT * FROM sys.fn_helpcollations()
go
-- Create a database to play in and move there.
CREATE DATABASE Latin1_General_CI_AS COLLATE Latin1_General_CI_AS
go
USE Latin1_General_CI_AS
go

-- Create a table and add some sample data.
CREATE TABLE sortwords (no     int           NOT NULL,
                        word   nvarchar(50)  NOT NULL,
                        remark nvarchar(100) NULL,
                        CONSTRAINT pk_sortwords PRIMARY KEY (no)
)

-- Note that the ordering agrees with the sort order of the collation Latin1_General_CI_AS.
INSERT sortwords(no, word, remark)
   VALUES (1,  N'Aarhus',      'Second biggest city in Denmark'),
          (2,  N'ål',          'Swedish for "eel"'),
          (3,  N'centre',      NULL),
          (4,  N'chicos',      'Spanish for "children"'),
          (5,  N'csárdás',     'Hungarian dance'),
          (6,  N'cycle',       NULL),
          (7,  N'ñandú',       'Rhea, an ostrich-like bird'),
          (8,  N'Netherlands', NULL),
          (9,  N'Oktober',     'German and Swedish spelling for October'),
          (10, N'Önnestad',    'Village where I grew up'),
          (11, N'Osnabrück',   'City in Germany'),
          (12, N'Pécs',        'City in Hungary'),
          (13, N'zuppa',       'Italian for "soup"')
          
-- First run the statement as is, then activate the COLLATE clauses to see how
-- these languages sort the strings. 
SELECT no, word, remark
FROM   sortwords
ORDER  BY word
   -- COLLATE German_PhoneBook_CI_AS
   -- COLLATE Traditional_Spanish_CI_AS
   -- COLLATE Hungarian_CI_AS
   -- COLLATE Finnish_Swedish_CI_AS
   -- COLLATE Danish_Greenlandic_100_CI_AS
go
